﻿using GPS.MODELS;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using GPS.DataAccessLayer;
using AutoMapper;
using PagedList;
using GPS.ENTITYS;
using GPS.COMMON;
using System.IO;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using Microsoft.AspNet.Identity;
using NPOI.HSSF.UserModel;
using OfficeOpenXml;
using System.Web.UI.WebControls;
using System.Web.UI;
using System.Net.Mime;

namespace GPS.Controllers.GPS_BaoCao
{
    public class GPS_DT_TN_PC_ChamThiController : Controller
    {
        DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
        #region Get user
        public GPS.MODELS.ApplicationUser getCurrentUser()
        {
            ApplicationDbContext dbUser = new ApplicationDbContext();
            string userId = User.Identity.GetUserId();
            ApplicationUser _currentUser = dbUser.Users.Find(userId);
            return _currentUser;
        }
        #endregion

        public void initialCategorySearchAction(View_Export_DT_PC_ChamThi viewModel)
        {
            ViewBag.checkuser = this.getCurrentUser().MA_TINHTP;
            string getdvgtvt_Ma = GPS.DAL.Check_User.CheckUser(this.getCurrentUser().MA_TINHTP, viewModel.pDVGTVT_MA);
            viewModel.pDVGTVT_MA = getdvgtvt_Ma;

            string _QD_ID = viewModel.pQD_ID;
            GPS.DAL.Check_User.Substring_Autocomplate(_QD_ID, ref _QD_ID);
            viewModel.pQD_ID = _QD_ID;

            var lstPhanCong = _db.Filter<SCVSHV>(o => o.CVSHV_CV == "CHAMTHIHINH"|| o.CVSHV_CV == "CHAMTHIDUONG"|| o.CVSHV_CV == "CHAMTHILUAT");
            ViewBag.LstPhanCong = new SelectList(lstPhanCong, "CVSHV_MA", "CVSHV_TEN");
        }

        #region Index
        public ActionResult Index(View_Export_DT_PC_ChamThi SearchModel, int? currentPage)
        {
            initialCategorySearchAction(SearchModel);
            List<CrudmodelDT_PC_ChamThi> model = new List<CrudmodelDT_PC_ChamThi>();
            if (!string.IsNullOrEmpty(SearchModel.pQD_ID))
            {
                model = GPS_EXPORT_GETLIST_PCCHAMTHI(SearchModel.pDVGTVT_MA, SearchModel.pQD_ID, null);
            }

            Int16 icount = 1;
            foreach (var item in model)
            {
                item.STT = icount.ToString();
                icount++;
            }
            ViewBag.TotalRow = model.Count == 0 ? "0" : model.Count.ToString();
            if (SearchModel.SearchButton == "Xuất Excel")
            {
                DataSet ds = RunPKG(SearchModel);
                Export_DT_PC_ChamThi _model = new Export_DT_PC_ChamThi();
                if (ds != null)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        _model.DVGTVT_TEN = ds.Tables[0].Rows[i]["DVGTVT_TEN"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_TEN"].ToString();
                        _model.DDTTN_QD_SO_QD = ds.Tables[0].Rows[i]["DDTTN_QD_SO_QD"] == null ? "" : ds.Tables[0].Rows[i]["DDTTN_QD_SO_QD"].ToString();
                        if (ds.Tables[0].Rows[i]["DDTTN_QD_NGAY_QD"] != null)
                        {
                            _model.DDTTN_QD_NGAY_QD = Convert.ToDateTime(ds.Tables[0].Rows[i]["DDTTN_QD_NGAY_QD"].ToString());
                        }
                        _model.DVGTVT_LANHDAO = ds.Tables[0].Rows[i]["DVGTVT_LANHDAO"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_LANHDAO"].ToString();
                        _model.DS_KH_TEN = ds.Tables[0].Rows[i]["DS_KH_TEN"] == null ? "" : ds.Tables[0].Rows[i]["DS_KH_TEN"].ToString();
                        _model.SHV_TOTRUONG = ds.Tables[0].Rows[i]["SHV_TOTRUONG"] == null ? "Chưa phân công tổ trường" : ds.Tables[0].Rows[i]["SHV_TOTRUONG"].ToString();
                    }
                    _model.DATE = DateTime.Now.ToShortDateString();
                    _model.TIME = DateTime.Now.ToShortTimeString();
                    _model.LISTpRetLuat = COMMON.Helpers.MapDataTableToList<Export_DT_PC_ChamThi>(ds.Tables[1]);
                    _model.LISTpRetHinh = COMMON.Helpers.MapDataTableToList<Export_DT_PC_ChamThi>(ds.Tables[2]);
                    _model.LISTpRetDuong = COMMON.Helpers.MapDataTableToList<Export_DT_PC_ChamThi>(ds.Tables[3]);
                    byte[] contentFile = null;
                    contentFile = GPS.ENTITYS.EXPORTWORD_DT_PC_ChamThi.EXPORT_DT_PC_ChamThi(Server.MapPath("~/Templates/GPS_Word/BienBanHopPhanCongChamThi.docx"), _model, "DocxGenericReport");
                    string nameOutput = string.Format("BienBanHopPhanCongChamThi_{0}_{1}.docx", SearchModel.pKH_MA, DateTime.Now.ToShortDateString().Replace("/", "_"));
                    return File(contentFile, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", nameOutput);
                }
            }
            var pageIndex = SearchModel.Page ?? 1;
            SearchModel.SearchResults = model.ToPagedList(pageIndex, GPS.COMMON.Constants.PageSize);
            return View(SearchModel);
        }
        #endregion

        [HttpGet]
        public ActionResult _Detail(string id1, string id2, string id3)
        {
            var listPhanCong = _db.Filter<SCVSHV>(o => o.CVSHV_CV == "CHAMTHIHINH" || o.CVSHV_CV == "CHAMTHIDUONG" || o.CVSHV_CV == "CHAMTHILUAT");
            ViewBag.LstPhanCong = new SelectList(listPhanCong, "CVSHV_MA", "CVSHV_TEN");
            CrudmodelDT_PC_ChamThi model = GPS_EXPORT_GETITEM_PCCHAMTHI(id1, id2, id3);
            return PartialView("_Detail", model);
        }

        [HttpPost]
        public ActionResult _Detail(CrudmodelDT_PC_ChamThi model, FormCollection form)
        {
            ViewBag.LstPhanCong = new SelectList(_db.Filter<SCVSHV>(o => o.CVSHV_MA == "15" || o.CVSHV_MA == "16" || o.CVSHV_MA == "17"), "CVSHV_MA", "CVSHV_TEN");
            if (ModelState.IsValid)
            {
                GPS_EXPORT_PCCHAMTHI_UPDATE(model.DTTN_HD_DVGTVT_MA, model.DTTN_HD_SHV_MA, model.DTTN_HD_DTTN_QD_ID, model.DTTN_HD_CVSHV_MA_PC);
                string url = Url.Action("Index", "GPS_DT_TN_PC_ChamThi");
                return Json(new { success = true, url = url });
            }
            else
            {
                return PartialView("_Detail", model);
            }
        }
        public DataSet RunPKG(View_Export_DT_PC_ChamThi View)
        {
            try
            {
                DataSet ds = OracleHelper.ExecuteDataset(_db.DbContext.Database.Connection.ConnectionString, CommandType.StoredProcedure, "GPS_DAOTAO.GPS_DAOTAO_DTTN_PC_CHAMTHI"
                                                         , new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(View.pDVGTVT_MA) ? DBNull.Value : (object)View.pDVGTVT_MA, ParameterDirection.Input)
                                                         , new OracleParameter("pDTTN_QD_ID", OracleDbType.Int64, string.IsNullOrWhiteSpace(View.pQD_ID) ? DBNull.Value : (object)View.pQD_ID, ParameterDirection.Input)
                                                         , new OracleParameter("pRetHeader", OracleDbType.RefCursor, ParameterDirection.Output)
                                                         , new OracleParameter("pRetLuat", OracleDbType.RefCursor, ParameterDirection.Output)
                                                         , new OracleParameter("pRetHinh", OracleDbType.RefCursor, ParameterDirection.Output)
                                                         , new OracleParameter("pRetDuong", OracleDbType.RefCursor, ParameterDirection.Output)
                                                        );

                return ds;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public List<CrudmodelDT_PC_ChamThi> GPS_EXPORT_GETLIST_PCCHAMTHI(string pDVGTVT_MA, string pQD_ID, string pDTTN_HD_SHV_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pDTTN_HD_SHV_MA = new OracleParameter("pDTTN_HD_SHV_MA", OracleDbType.Varchar2, (pDTTN_HD_SHV_MA == null ? DBNull.Value : (object)pDTTN_HD_SHV_MA), ParameterDirection.Input);
            var _pQD_ID = new OracleParameter("pQD_ID", OracleDbType.Decimal, (pQD_ID == null ? DBNull.Value : (object)pQD_ID), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<CrudmodelDT_PC_ChamThi>("BEGIN GPS_EXPORT.GPS_EXPORT_GETLIST_PCCHAMTHI(:pDVGTVT_MA,:pDTTN_HD_SHV_MA,:pQD_ID,:pRET); end;"
                                                                                                                        , _pDVGTVT_MA
                                                                                                                        , _pDTTN_HD_SHV_MA
                                                                                                                           , _pQD_ID
                                                                                                                        , _pRET
                                                                                                                        ).ToList();
            return datas;
        }

        public void GPS_EXPORT_PCCHAMTHI_UPDATE(string pDVGTVT_MA, string pDTTN_HD_SHV_MA, decimal? pQD_ID, string pDTTN_HD_CVSHV_MA_PC)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pDTTN_HD_SHV_MA = new OracleParameter("pDTTN_HD_SHV_MA", OracleDbType.Varchar2, (pDTTN_HD_SHV_MA == null ? DBNull.Value : (object)pDTTN_HD_SHV_MA), ParameterDirection.Input);
            var _pQD_ID = new OracleParameter("pQD_ID", OracleDbType.Decimal, (pQD_ID == null ? DBNull.Value : (object)pQD_ID), ParameterDirection.Input);
            var _pDTTN_HD_CVSHV_MA_PC = new OracleParameter("pDTTN_HD_CVSHV_MA_PC", OracleDbType.Varchar2, (pDTTN_HD_CVSHV_MA_PC == null ? DBNull.Value : (object)pDTTN_HD_CVSHV_MA_PC), ParameterDirection.Input);
            string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"];
            OracleHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure
                                                                , "GPS_EXPORT.GPS_EXPORT_PCCHAMTHI_UPDATE"
                                                                , _pDVGTVT_MA
                                                                , _pDTTN_HD_SHV_MA
                                                                , _pQD_ID
                                                                , _pDTTN_HD_CVSHV_MA_PC
                                                                );
        }

        public CrudmodelDT_PC_ChamThi GPS_EXPORT_GETITEM_PCCHAMTHI(string pDVGTVT_MA, string pQD_ID, string pDTTN_HD_SHV_MA)
        {
            DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (pDVGTVT_MA == null ? DBNull.Value : (object)pDVGTVT_MA), ParameterDirection.Input);
            var _pDTTN_HD_SHV_MA = new OracleParameter("pDTTN_HD_SHV_MA", OracleDbType.Varchar2, (pDTTN_HD_SHV_MA == null ? DBNull.Value : (object)pDTTN_HD_SHV_MA), ParameterDirection.Input);
            var _pQD_ID = new OracleParameter("pQD_ID", OracleDbType.Decimal, (pQD_ID == null ? DBNull.Value : (object)pQD_ID), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var datas = _db.DbContext.Database.SqlQuery<CrudmodelDT_PC_ChamThi>("BEGIN GPS_EXPORT.GPS_EXPORT_GETLIST_PCCHAMTHI(:pDVGTVT_MA,:pDTTN_HD_SHV_MA,:pQD_ID,:pRET); end;"
                                                                                                                        , _pDVGTVT_MA
                                                                                                                        , _pDTTN_HD_SHV_MA
                                                                                                                        , _pQD_ID
                                                                                                                        , _pRET
                                                                                                                        ).FirstOrDefault();
            return datas;
        }
    }
}